/**
 * 
 */
package org.jinion.database.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.jinion.database.AbstractDatabase;
import org.jinion.database.SqlConstants;
import org.jinion.database.connectionPool.ConnectionManager;


/**
 * Database implementation for MySQL.<br>
 * <br>
 * <b>NOTE: NOT thread safe</b>
 * @author zhlmmc
 */
public class DatabaseMySql extends AbstractDatabase {
	/**
	 * Create a Database with the given connection.
	 * 
	 * @param connection
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 */
	public DatabaseMySql(ConnectionManager manager) throws ClassNotFoundException, SQLException {
		super(manager);
	}
	
	protected String buildQuerySql(String tableName, String orderBy,
			int ascOrDesc) throws SQLException {
		StringBuilder sb = new StringBuilder("");
		sb.append("SELECT * FROM " + tableName + " ");
		
		// add where clauses
		List<String> tableColumns = this.getColumns(tableName);
		
		String where = this.getWhereClause(tableColumns);
		if (where != null) {
			sb.append(where);
		}
		if (orderBy != null) {
			sb.append("ORDER BY " + orderBy + " ");
			if (ascOrDesc == SqlConstants.ASC) {
				sb.append("ASC");
			} else {
				sb.append("DESC");
			}
		}

		this.getWhereClauses().clear();
		
		return sb.toString();
	}
	
	protected String buildUpdateSql(String tableName, Map<Object, Object> dataMap)
			throws SQLException {
		
		if (dataMap == null) {
			return null;
		}
		
		StringBuilder sb = new StringBuilder("");
		List<String> columns = this.getColumns(tableName);
		sb.append("UPDATE " + tableName + " SET ");
		// SET clause
		Set<Object> keys = dataMap.keySet();
		for (Iterator<Object> iter = keys.iterator(); iter.hasNext();) {
			String key = (String) iter.next();
			// filter keys that don't belongs to this table
			if (columns.contains(key) && dataMap.get(key) != null) {
				Object obj = dataMap.get(key);
				if (obj instanceof Date) {
					Timestamp time = new Timestamp(((Date)obj).getTime());
					obj = time;
				}
				String value = this.fixChars(obj.toString());
				sb.append(key + " = '" + value + "', ");
			}
			else if(key.startsWith("#")){
				int index = Integer.parseInt(key.substring(1)) - 1;
				String column = columns.get(index).toString();
				String value = this.fixChars(dataMap.get(key).toString());
				sb.append(column + " = '" + value + "', ");
			}
		}
		
		sb = sb.delete(sb.length() - 2, sb.length() - 1);

		// add where clauses
		String where = this.getWhereClause(columns);
		if (where != null) {
			sb.append(where);
		}

		this.getWhereClauses().clear();

		return sb.toString();
	}

	protected String buildInsertSql(String tableName, Map<Object, Object> dataMap) throws SQLException {
		StringBuilder sb = new StringBuilder("");
		List<String> columns = this.getColumns(tableName);
		sb.append("INSERT INTO " + tableName + " ");
		sb.append("(");
		// SET clause
		StringBuilder sb2 = new StringBuilder("VALUES (");
		
		Set<Object> keys = dataMap.keySet();
		for (Iterator<Object> iter = keys.iterator(); iter.hasNext();) {
			Object obj = iter.next();
			if (obj == null) {
				continue;
			}
			String key = (String) obj;
			// filter keys that don't belongs to this table
			if (columns.contains(key) && dataMap.get(key) != null) {
				//String value = map.get(element).toString();
				Object valueObj = dataMap.get(key);
				if (valueObj instanceof Date) {
					Date temp = (Date)valueObj;
					Timestamp date = new Timestamp(temp.getTime());
					valueObj = date;
				}
				String value = this.fixChars(valueObj.toString());
				sb.append(key + ", ");
				sb2.append("'" + value + "', ");
			}
			else if(key.startsWith("#")){
				int index = Integer.parseInt(key.substring(1)) - 1;
				String column = columns.get(index).toString();
				String value = this.fixChars(dataMap.get(key).toString());
				sb.append(column + ", ");
				sb2.append("'" + value + "', ");
			}
		}
		
		sb.delete(sb.length() - 2, sb.length());
		sb2.delete(sb2.length() - 2, sb2.length() - 1);
		sb.append(") ");
		sb2.append(") ");
		sb.append(sb2.toString());

		// add where clauses
		String where = this.getWhereClause(columns);
		if (where != null) {
			sb.append(where);
		}

		this.getWhereClauses().clear();

		return sb.toString();
	}

	protected String buildDeleteSql(String tableName) throws SQLException {
		StringBuilder sb = new StringBuilder("");
		sb.append("DELETE FROM " + tableName + " ");

		// add where clauses
		String where = this.getWhereClause(this.getColumns(tableName));
		if (where != null) {
			sb.append(where);
		}

		this.getWhereClauses().clear();

		return sb.toString();
	}

	/**
	 * This medhod assumes that if the string of <i>value</i> is the same with
	 * any column name, <br>
	 * the <i>value</i> denotes the column not the string. If you want to use
	 * the string of the <br>
	 * <i>value</i> instead of the column, add single quotes around <i>value</i>.
	 * 
	 * @param tableColumns
	 * @return
	 */
	private String getWhereClause(List<String> tableColumns) {
		StringBuilder sb = new StringBuilder("");

		if (!this.getWhereClauses().isEmpty()) {
			WhereClause wc = (WhereClause) this.getWhereClauses().get(0);
			wc.setValue(this.fixChars(wc.getValue().toString()));
			// column value or string value
			if (this.existsInColumns(wc.getValue().toString(), tableColumns)) {
				sb.append("WHERE " + wc.getFieldName() + " "
						+ this.getCompareString(wc.getCompareType()) + " "
						+ wc.getValue() + " ");
			} else {
				sb.append("WHERE " + wc.getFieldName() + " "
						+ this.getCompareString(wc.getCompareType()) + " '"
						+ wc.getValue() + "' ");
			}

			for (int i = 1; i < this.getWhereClauses().size(); i++) {
				wc = (WhereClause) this.getWhereClauses().get(i);
				wc.setValue(this.fixChars(wc.getValue().toString()));
				sb.append(this.getConnectionString(wc.getClauseType()) + " ");
				// column value or string value
				if (this.existsInColumns(wc.getValue().toString(), tableColumns)) {
					sb.append(wc.getFieldName() + " "
							+ this.getCompareString(wc.getCompareType()) + " "
							+ wc.getValue() + " ");
				} else {
					sb.append(wc.getFieldName() + " "
							+ this.getCompareString(wc.getCompareType()) + " '"
							+ wc.getValue() + "' ");
				}

			}
		} else {
			return null;
		}
		return sb.toString();
	}

	private boolean existsInColumns(String name, List<String> tableColumns) {
		
		return tableColumns.contains(name);
	}

	/**
	 * 
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	private List<String> getColumns(String tableName) throws SQLException {
		String sql = "SHOW COLUMNS FROM " + tableName;
		List<String> list = new ArrayList<String>();

		ResultSet rs = getConnection().createStatement().executeQuery(sql);
		while (rs.next()) {
			list.add(rs.getString(1));
		}
		
		releaseConnection();
		
		return list;
	}
	
	/**
	 * fix escape characters " ' " and " \ ".
	 * @param str
	 * @return
	 */
	private String fixChars(String str){
		int pos = 0;
		while (pos < str.length()) {
			int index =  str.indexOf('\\', pos);
			if (index >= 0) {
				String sub = str.substring(0, index);
				sub = sub.concat("\\");
				sub = sub.concat(str.substring(index));
				str = sub;
				pos = index + 2;
			}
			else{
				break;
			}
		}
		
		pos = 0;
		while (pos < str.length()) {
			int index =  str.indexOf('\'', pos);
			if (index >= 0) {
				String sub = str.substring(0, index);
				sub = sub.concat("\'");
				sub = sub.concat(str.substring(index));
				str = sub;
				pos = index + 2;
			}
			else{
				break;
			}
		}
		//System.out.println(str);
		return str;
	}

	/**
	 * 
	 * @param clauseType
	 * @return
	 */
	private String getConnectionString(int clauseType) {
		switch (clauseType) {
		case SqlConstants.AND:
			return "AND";
		case SqlConstants.OR:
			return "OR";
		}

		return null;
	}

	/**
	 * 
	 * @param compareType
	 * @return
	 */
	private String getCompareString(int compareType) {
		String str = null;
		switch (compareType) {
		case SqlConstants.EQUEAL:
			str = "=";
			break;
		case SqlConstants.GREATER_THAN:
			str = ">";
			break;
		case SqlConstants.LESS_THAN:
			str = "<";
			break;
		case SqlConstants.UNEQUAL:
			str = "<>";
			break;
		case SqlConstants.GREATER_OR_EQUAL:
			str = ">=";
			break;
		case SqlConstants.LESS_OR_EQUAL:
			str = "<=";
			break;
		case SqlConstants.LIKE:
			str = "LIKE";
			break;
		}

		return str;
	}	
}
